﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using StudySystem.MainLogic.Items;
using StudySystem.MainLogic;

namespace StudySystem.SQLServer
{
    /**
     * 纯数据库操作类
     * 通常不直接被控件的事件调用
     * */
    public static class DbHelper
    {
        //数据库连接字符串
        private readonly static string ConnStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        #region    不用要删除的函数
        ///// <summary>
        ///// 使用参数方法查询数据库，重要用于辨别用户身份****************舍弃
        ///// </summary>
        ///// <param name="id">用户id</param>
        ///// <param name="password">密码</param>
        ///// <returns>用返回值是否为null来判断是否查询成功</returns>
        //public static object SafeExecuteScalar(string id, string password)
        //{
        //    object o = null;
        //    SqlParameter paraId = new SqlParameter("@userId", SqlDbType.NChar);
        //    paraId.Value = id;
        //    SqlParameter paraPwd = new SqlParameter("@password", SqlDbType.NChar);
        //    paraPwd.Value = password;
        //    string sqlString = "select * from UserTable where UserId = @userId and PassWord = @password";
        //    SqlConnection sqlConnection = new SqlConnection(ConnStr);
        //    SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection);
        //    sqlCommand.Parameters.Add(paraId);
        //    sqlCommand.Parameters.Add(paraPwd);
        //    sqlConnection.Open();
        //    o = sqlCommand.ExecuteScalar();
        //    sqlConnection.Close();

        //    return o;
        //}

        ///// <summary>
        ///// 使用参数方法查询数据库，主要用于辨别用户身份
        ///// </summary>
        ///// <param name="id">用户id</param>
        ///// <param name="password">密码</param>
        ///// <returns>用返回值是否为null来判断是否查询成功</returns>
        //public static UserInformation GetUserInformation(string id, string password)
        //{
        //    UserInformation user = null;
        //    SqlParameter paraId = new SqlParameter("@userId", SqlDbType.NChar);
        //    paraId.Value = id;
        //    SqlParameter paraPwd = new SqlParameter("@password", SqlDbType.NChar);
        //    paraPwd.Value = password;
        //    string sqlString = "select * from UserTable where UserId = @userId and PassWord = @password";
        //    SqlDataReader dr = GetSafeDataReader(sqlString, paraId, paraPwd);
        //    while (dr.Read())
        //    {
        //        user = new UserInformation();
        //        user.UserID = (string)dr[0];
        //        user.UserName = (string)dr[1];
        //    }
        //    dr.Close();
        //    return user;
        //}

        ///// <summary>
        ///// 获得当前用户的所有科目
        ///// </summary>
        ///// <param name="userId"></param>
        ///// <param name="ds"></param>
        //public static SqlDataAdapter GetSubjects(string userId,ref DataSet ds)
        //{
        //    string sql = "select SubjectTable.SubjectId, SubjectName,count(QuestionTable.SubjectID) QuesCount " +
        //        "from SubjectTable,QuestionTable on s.SubjectID = q.SubjectID where SubjectTable.UserId = " + userId + 
        //        " and SubjectTable.SubjectID = QuestionTable.SubjectID " +
        //        "group by SubjectTable.SubjectID, SubjectName";
        //    return GetTable(sql, ref ds, "subjectTable");
        //}

        ///// <summary>
        ///// 获得指定科目的所有问题
        ///// </summary>
        ///// <param name="subjectId"></param>
        ///// <param name="ds"></param>
        //public static void GetQuestions(string subjectId, ref DataSet ds)
        //{
        //    string sql = "select QuestionID,QuestionContent,Answer  from QuestionTable where UserID = " + UserManagement.user.UserId;
        //    GetTable(sql, ref ds, "questionTable");
        //}

        ///// <summary>
        ///// 插入一个学科（学科ID是自动生成）
        ///// </summary>
        ///// <param name="subjectName"></param>
        ///// <returns>受影响行数</returns>
        //public static int InsertASubject(string subjectName)
        //{
        //    string sql = "insert into SubjectTable(UserID, SubjectName) " +
        //        "values('" + UserManagement.user.UserID + "', '" + subjectName + "')";
        //    return ModifyRecord(sql);
        //}


        //public static int SafeInsertASubject(string subjectName)
        //{
        //    string sql = "insert into SubjectTable(UserID, SubjectName) " +
        //        "values(@UserID,@SubjectName);";
        //    SqlParameter paraUserID = new SqlParameter("@UserID", SqlDbType.NChar);
        //    paraUserID.Value = UserManagement.user.UserID;
        //    SqlParameter paraSubName = new SqlParameter("@SubjectName", SqlDbType.NVarChar);
        //    paraSubName.Value = subjectName;

        //    return SafeExecuteNonQuery(sql, paraUserID, paraSubName);
        //}

        ///// <summary>
        ///// 通过学科ID删除一个学科
        ///// </summary>
        ///// <param name="subjectID"></param>
        ///// <returns></returns>
        //public static int DeleteASubject(int subjectID)
        //{
        //    string sql = "delete from RecordTable where subjectID = " + subjectID + ";" +
        //        "delete from QuestionTable where subjectID = " + subjectID + "; " +
        //        "delete from SubjectTable where subjectID =" + subjectID + ";";
        //    return ModifyRecord(sql);
        //}

        /// <summary>
        /// 修改学科名称
        /// </summary>
        /// <param name="subjectID"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        //public static int ModifySubName(int subjectID, string newName)
        //{
        //    string sql = "update SubjectTable set SubjectName = '"
        //        + newName + "' where subjectID = " + subjectID + ";";
        //    return ModifyRecord(sql);
        //}

        //public static int SafeModifySubName(int subjectID, string newName)
        //{
        //    string sql = "update SubjectTable set SubjectName = @paraSubName where SubjectID = @paraSubID";
        //    SqlParameter paraSubName = new SqlParameter("@paraSubName", SqlDbType.NVarChar);
        //    paraSubName.Value = newName;
        //    SqlParameter paraSubID = new SqlParameter("@paraSubID", SqlDbType.Int);
        //    paraSubID.Value = subjectID;
        //    return SafeExecuteNonQuery(sql, paraSubName, paraSubID);
        //}

        //public static int InsertAQuestion(string subjectID, string question, string answer)
        //{
        //    string sql = "insert into QuestionTable(UserID,SubjectID,QuestionContent,Answer) values('" +
        //        UserManagement.user.UserID + "','" + subjectID + "','" + question + "','" + answer + "')";
        //    return ModifyRecord(sql);
        //}

        //public static int DeleteAQuestion(string questionID)
        //{
        //    string sql = "delete from QuestionTable where questionID =" + questionID + ";";
        //    return ModifyRecord(sql);
        //}


        //public static int SafeModifyQuestion(string question, string answer, int questionID)
        //{
        //    string sql = "update QuestionTable set QuestionContent = @paraQues, " +
        //        "Answer = @paraAnswer where QuestionID = @paraQuesID";
        //    SqlParameter paraQues = new SqlParameter("@paraQues", SqlDbType.NVarChar);
        //    paraQues.Value = question;
        //    SqlParameter paraAnswer = new SqlParameter("@paraAnswer", SqlDbType.NVarChar);
        //    paraAnswer.Value = answer;
        //    SqlParameter paraQuesID = new SqlParameter("@paraQuesID", SqlDbType.Int);
        //    paraQuesID.Value = questionID;
        //    return SafeExecuteNonQuery(sql, paraQues, paraAnswer, paraQuesID);
        //}

        ///// <summary>
        ///// 获得当前用户的所有问题
        ///// </summary>
        ///// <param name="ds">内存的ds</param>
        //public static SqlDataAdapter GetQuestions(ref DataSet ds)
        //{
        //    string sql = "select SubjectID,QuestionID,QuestionContent,Answer  from QuestionTable where UserID = " + UserManagement.user.UserID;
        //    return GetTable(sql, ref ds, "questionTable");
        //}
        #endregion







        /// <summary>
        /// 内部使用的函数用来生成datareader对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">SqlParameter参数</param>
        /// <returns>dr对象</returns>
        public static SqlDataReader GetSafeDataReader(string sql,params SqlParameter[] parameters)
        {
            SqlDataReader dr = null;
            SqlConnection sqlConnection = new SqlConnection(ConnStr);
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            sqlCommand.Parameters.AddRange(parameters);
            try
            {
                sqlConnection.Open();
                dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                sqlConnection.Close();
                throw new Exception("执行查询异常！",ex);
            }
            return dr;
        }

        public static int SafeExecuteNonQuery(string sql, params SqlParameter [] parameters)
        {
            int ret = 0;
            using (SqlConnection sqlConnection = new SqlConnection(ConnStr))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlCommand.Parameters.AddRange(parameters);
                sqlConnection.Open();
                ret = sqlCommand.ExecuteNonQuery();
            }
            return ret;
        }


        /// <summary>
        /// 内部调用用来生成DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static SqlDataAdapter GetTable(string sql,ref DataSet ds, string tableName)
        {
            SqlConnection sqlConnection = new SqlConnection(ConnStr);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            if(ds.Tables[tableName] != null)
                ds.Tables[tableName].Clear();
            sqlDataAdapter.Fill(ds, tableName);
            return sqlDataAdapter;
        }


        



       


        /// <summary>
        /// 用来修个数据库中数据的函数
        /// 主要使用ExecuteNonQuery函数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>受影响行数</returns>
        public static int ModifyRecord(string sql)
        {
            int ret = 0;
            using (SqlConnection sqlConnection = new SqlConnection(ConnStr))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlConnection.Open();
                ret = sqlCommand.ExecuteNonQuery();
                //return ret;
            }
            return ret;
        }

       


        

        






    }
}
